﻿using System.ComponentModel;
using System.Data.OleDb;
using Entity;

namespace DAO
{
    public class QuanLyNhomDAO
    {
        static string connectionString = new connection().getConnectionString();

        public BindingList<QuanLyNhom> LoadAll()
        {
            BindingList<QuanLyNhom> list = new BindingList<QuanLyNhom>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from QuanLyNhom;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                int ma = (int)reader["MaNguoiDung"];
                int ten = (int)reader["MaNhomTin"];
                QuanLyNhom kv = new QuanLyNhom();
                kv.MaNguoiDung = ma;
                kv.MaNhomTin = ten;
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }
        public int DeleteByMaNguoiDung(int manguoidung)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM QuanLyNhom WHERE manguoidung = @manguoidung;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@manguoidung", OleDbType.Integer).Value = manguoidung;

            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public int DeleteByMaNhomTin(int manhomtin)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM QuanLyNhom WHERE manhomtin = @manhomtin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@manhomtin", OleDbType.Integer).Value = manhomtin;
            
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public int DeleteByStringId(int manguoidung, int manhomtin)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM QuanLyNhom WHERE manguoidung = @manguoidung AND manhomtin = @manhomtin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@manguoidung", OleDbType.Integer).Value = manguoidung;
            command.Parameters.Add("@manhomtin", OleDbType.Integer).Value = manhomtin;

            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public int Insert(QuanLyNhom kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "INSERT INTO QuanLyNhom (MaNhomTin,MaNguoiDung) VALUES(@MaNhomTin,@MaNguoiDung);";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaNhomTin", OleDbType.VarChar).Value = kv.MaNhomTin;
            command.Parameters.Add("@MaNguoiDung", OleDbType.VarChar).Value = kv.MaNguoiDung;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
       
    }
}
